﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace sistemaAcademicoFinanceiroEscolas
{
    public partial class definirProgressao : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlc.Open();


                string codigoSerie = "";

                string codigoAnoLetivo = "";

                //resgatar dados prévios da matrícula 


                string sqlProg = "select * from matricula where codigoMatricula =" + Request["codigoMatricula"].ToString();
                SqlCommand cmdProg = new SqlCommand(sqlProg, sqlc);
                SqlDataReader myReaderProg = cmdProg.ExecuteReader();

                while (myReaderProg.Read())
                {
                    codigoSerie = myReaderProg["codigoSerie"].ToString();

                    codigoAnoLetivo = myReaderProg["codigoAnoLetivo"].ToString();


                }

                cmdProg.Dispose();
                myReaderProg.Close();

                //Escrever o nome do aluno

                codigoAluno.Value = Request.QueryString["codigoAluno"];

                //Resgatar o nome do aluno
                string q = "select codigoAluno, nomeAluno from aluno where codigoAluno=" + Request.QueryString["codigoAluno"];
                SqlCommand cmd = new SqlCommand(q, sqlc);
                SqlDataReader myReader = cmd.ExecuteReader();

                while (myReader.Read())
                {
                    string nomeAluno = myReader["nomeAluno"].ToString();

                    lblAlunoNome.Text = nomeAluno;
                }

                //nome do aluno escrito

                cmd.Dispose();
                myReader.Close();
                //Resgata o ano letivo
                string serie = "select codigoAnoLetivo, descricaoAnoLetivo from anoLetivo where codigoAnoLetivo=" + codigoAnoLetivo;
                SqlCommand cmdSerie = new SqlCommand(serie, sqlc);
                SqlDataReader myReaderSerie = cmdSerie.ExecuteReader();

                while (myReaderSerie.Read())
                {
                    string nomeAno = myReaderSerie["descricaoAnoLetivo"].ToString();

                    lblAlunoAno.Text = nomeAno;
                }

                //Resgata a serie

                cmdSerie.Dispose();
                myReaderSerie.Close();

                string anoLet = "select codigoSerie, descricaoSerie from serie where codigoSerie=" + codigoSerie;
                SqlCommand cmdAnoLet = new SqlCommand(anoLet, sqlc);
                SqlDataReader myReaderAnoLe = cmdAnoLet.ExecuteReader();

                while (myReaderAnoLe.Read())
                {
                    string nomeSerie = myReaderAnoLe["descricaoSerie"].ToString();

                    lblAlunoSerie.Text = nomeSerie;


                }

                //nome do aluno escrito

                cmdSerie.Dispose();
                myReaderAnoLe.Close();

                string datas = " select * from valorProgressao";
                SqlCommand dates = new SqlCommand(datas, sqlc);
                SqlDataReader myReaderdate = dates.ExecuteReader();


                dates.Dispose();
                myReaderdate.Close();
            }

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            SqlConnection sqlc2 = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc2.Open();

            string sqlProfessores = "select codigoDisciplina, descricaoDisciplina from disciplina order by descricaoDisciplina";

            SqlCommand cmdProfessor = new SqlCommand(sqlProfessores, sqlc2);
            SqlDataReader myReaderProfessor = cmdProfessor.ExecuteReader();

            int cont = 0;

            while (myReaderProfessor.Read())
            {
                string valorRec = Request[myReaderProfessor["codigodisciplina"].ToString()];

                if (valorRec == "on")
                {
                    cont++;
                }
            }

            myReaderProfessor.Close();
            sqlc2.Close();


            if (cont <= 3)
            {
                SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlc.Open();

                //deletar todos os professores da disciplina

                string sqlDeletar = "delete from progressao where codigoMatricula =" + Request["codigoMatricula"].ToString();
                SqlDataReader myReader = null;

                SqlCommand cmdDel = new SqlCommand(sqlDeletar, sqlc);

                myReader = cmdDel.ExecuteReader();
                cmdDel.Dispose();

                //cadastrar as disciplinas que estavam marcados nos checkbox

                //loop em disciplinas

                sqlc2.Open();

                cmdProfessor = new SqlCommand(sqlProfessores, sqlc2);
                myReaderProfessor = cmdProfessor.ExecuteReader();

                while (myReaderProfessor.Read())
                {


                    string valorRec = Request[myReaderProfessor["codigodisciplina"].ToString()];

                    if (valorRec == "on")
                    {

                        //inserir registro

                        SqlConnection sqlc3 = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                        sqlc3.Open();

                        string pegacodigo = "select Max(codigoValorProgressao)as codigoValorProgressao from valorProgressao";
                        SqlCommand cmddCod = new SqlCommand(pegacodigo, sqlc3);
                        SqlDataReader myReadercod = cmddCod.ExecuteReader();
                        while (myReadercod.Read())
                        {
                            string insProfessorDisciplina = "insert into progressao(codigoMatricula, codigoDisciplina, codigoValorProgressao)";
                            insProfessorDisciplina = insProfessorDisciplina + " values (" + Request["codigoMatricula"].ToString();
                            insProfessorDisciplina = insProfessorDisciplina + "," + myReaderProfessor["codigodisciplina"].ToString() + ",";
                            insProfessorDisciplina = insProfessorDisciplina + myReadercod["codigoValorProgressao"].ToString() + ")";

                            SqlCommand cmdIns = new SqlCommand(insProfessorDisciplina, sqlc3);
                            SqlDataReader myReaderIns = cmdIns.ExecuteReader();

                            cmdIns.Dispose();

                        }
                        cmddCod.Dispose();
                        myReadercod.Close();
                        sqlc3.Close();
                    }
                }
                sqlc.Close();

                Response.Redirect("progressoesDefinidas.aspx");
            }
            else
            {

                //mensagem java script
                string literalcc = "<script language=JavaScript>erro();</script>";
                Panel1.Controls.Add(new LiteralControl(literalcc));
            }
        }
    }
}
